#include <stdio.h>
#include <string.h>
#include <time.h>
#include "stu_db.h"


// 执行查询并返回StuRecord数组
int fetch_stu_records(MYSQL *conn, StuRecord **records, unsigned long *num_records) {
    MYSQL_RES *res = get_query_result(conn, "SELECT id, create_time, name FROM stu");
    if (res == NULL)
        return -1;

    *num_records = mysql_num_rows(res);
    *records = (StuRecord *)malloc(*num_records * sizeof(StuRecord));
    if (*records == NULL) {
        fprintf(stderr, "Memory allocation failed\n");
        mysql_free_result(res);
        return -1;
    }

    MYSQL_ROW row;
    unsigned long i = 0;
    while ((row = mysql_fetch_row(res)) != NULL) {
        (*records)[i].id = atoi(row[0]); // 将字符串转换为整数
        strncpy((*records)[i].create_time, row[1], sizeof((*records)[i].create_time) - 1);
        strncpy((*records)[i].name, row[2], sizeof((*records)[i].name) - 1);
        (*records)[i].name[sizeof((*records)[i].name) - 1] = '\0'; // 确保字符串以null终止
        ++i;
    }

    mysql_free_result(res);
    return 0;
}

// 实现：向stu表中插入一条记录
int insert_stu_record(MYSQL *conn, const StuRecord *record) {
    MYSQL_STMT *stmt;
    MYSQL_BIND bind[3];
    char query[256];

    // 准备SQL语句
    snprintf(query, sizeof(query), "INSERT INTO stu (id, create_time, name) VALUES (?, ?, ?)");
    stmt = mysql_stmt_init(conn);
    if (!stmt) {
        fprintf(stderr, "mysql_stmt_init(), out of memory\n");
        return -1;
    }
    if (mysql_stmt_prepare(stmt, query, strlen(query))) {
        fprintf(stderr, "mysql_stmt_prepare(), INSERT failed\n");
        fprintf(stderr, "errno = %d\n", mysql_stmt_errno(stmt));
        fprintf(stderr, "error = %s\n", mysql_stmt_error(stmt));
        mysql_stmt_close(stmt);
        return -1;
    }

    // 绑定参数
    memset(bind, 0, sizeof(bind));

    bind[0].buffer_type = MYSQL_TYPE_LONG;
    bind[0].buffer = (char *)&(record->id);

    bind[1].buffer_type = MYSQL_TYPE_STRING;
    bind[1].buffer = (char *)record->create_time;
    bind[1].buffer_length = strlen(record->create_time);

    bind[2].buffer_type = MYSQL_TYPE_STRING;
    bind[2].buffer = (char *)record->name;
    bind[2].buffer_length = strlen(record->name);

    if (mysql_stmt_bind_param(stmt, bind)) {
        fprintf(stderr, "mysql_stmt_bind_param(), INSERT failed\n");
        fprintf(stderr, "errno = %d\n", mysql_stmt_errno(stmt));
        fprintf(stderr, "error = %s\n", mysql_stmt_error(stmt));
        mysql_stmt_close(stmt);
        return -1;
    }
    // 执行预处理语句
    if (mysql_stmt_execute(stmt)) {
        fprintf(stderr, "mysql_stmt_execute(), INSERT failed\n");
        fprintf(stderr, "errno = %d\n", mysql_stmt_errno(stmt));
        fprintf(stderr, "error = %s\n", mysql_stmt_error(stmt));
        mysql_stmt_close(stmt);
        return -1;
    }
    
    // 关闭预处理语句
    mysql_stmt_close(stmt);

    // 检查是否有行受到影响
    if (mysql_affected_rows(conn) <= 0) {
        fprintf(stderr, "No rows were affected by the INSERT operation.\n");
        return -1;
    }

    printf("Record inserted successfully.\n");
    return 0;
}

// 实现：根据ID删除stu表中的一条记录
int delete_stu_record(MYSQL *conn, int id) {
    MYSQL_STMT *stmt;
    MYSQL_BIND bind[1];
    char query[256];

    // 准备SQL语句
    snprintf(query, sizeof(query), "DELETE FROM stu WHERE id = ?");
    stmt = mysql_stmt_init(conn);
    if (!stmt) {
        fprintf(stderr, "mysql_stmt_init(), out of memory\n");
        return -1;
    }
    if (mysql_stmt_prepare(stmt, query, strlen(query))) {
        fprintf(stderr, "mysql_stmt_prepare(), DELETE failed\n");
        fprintf(stderr, "errno = %d\n", mysql_stmt_errno(stmt));
        fprintf(stderr, "error = %s\n", mysql_stmt_error(stmt));
        mysql_stmt_close(stmt);
        return -1;
    }

    // 绑定参数
    memset(bind, 0, sizeof(bind));

    bind[0].buffer_type = MYSQL_TYPE_LONG;
    bind[0].buffer = (char *)&id;

    if (mysql_stmt_bind_param(stmt, bind)) {
        fprintf(stderr, "mysql_stmt_bind_param(), DELETE failed\n");
        fprintf(stderr, "errno = %d\n", mysql_stmt_errno(stmt));
        fprintf(stderr, "error = %s\n", mysql_stmt_error(stmt));
        mysql_stmt_close(stmt);
        return -1;
    }

    // 执行预处理语句
    if (mysql_stmt_execute(stmt)) {
        fprintf(stderr, "mysql_stmt_execute(), DELETE failed\n");
        fprintf(stderr, "errno = %d\n", mysql_stmt_errno(stmt));
        fprintf(stderr, "error = %s\n", mysql_stmt_error(stmt));
        mysql_stmt_close(stmt);
        return -1;
    }

    // 关闭预处理语句
    mysql_stmt_close(stmt);

    // 检查是否有行受到影响
    if (mysql_affected_rows(conn) <= 0) {
        fprintf(stderr, "No rows were affected by the DELETE operation.\n");
        return -1;
    }

    printf("Record deleted successfully.\n");
    return 0;
}
// 实现：更新stu表中的一条记录
int update_stu_record(MYSQL *conn, const StuRecord *record) {
    MYSQL_STMT *stmt;
    MYSQL_BIND bind[3];
    char query[256];

    // 设置会话时区为UTC
    if (mysql_query(conn, "SET time_zone = '+00:00'")) {
        fprintf(stderr, "Failed to set session time zone\n");
        return -1;
    }

    // 准备SQL语句
    snprintf(query, sizeof(query), "UPDATE stu SET create_time = ?, name = ? WHERE id = ?");
    stmt = mysql_stmt_init(conn);
    if (!stmt) {
        fprintf(stderr, "mysql_stmt_init(), out of memory\n");
        return -1;
    }
    if (mysql_stmt_prepare(stmt, query, strlen(query))) {
        fprintf(stderr, "mysql_stmt_prepare(), UPDATE failed\n");
        fprintf(stderr, "errno = %d\n", mysql_stmt_errno(stmt));
        fprintf(stderr, "error = %s\n", mysql_stmt_error(stmt));
        mysql_stmt_close(stmt);
        return -1;
    }

    // 绑定参数
    memset(bind, 0, sizeof(bind));


    bind[0].buffer_type = MYSQL_TYPE_STRING;
    bind[0].buffer = (char *)record->create_time;
    bind[0].buffer_length = strlen(record->create_time);

    bind[1].buffer_type = MYSQL_TYPE_STRING;
    bind[1].buffer = (char *)record->name;
    bind[1].buffer_length = strlen(record->name);

    bind[2].buffer_type = MYSQL_TYPE_LONG;
    bind[2].buffer = (char *)&(record->id);

    if (mysql_stmt_bind_param(stmt, bind)) {
        fprintf(stderr, "mysql_stmt_bind_param(), UPDATE failed\n");
        fprintf(stderr, "errno = %d\n", mysql_stmt_errno(stmt));
        fprintf(stderr, "error = %s\n", mysql_stmt_error(stmt));
        mysql_stmt_close(stmt);
        return -1;
    }

    // 执行预处理语句
    if (mysql_stmt_execute(stmt)) {
        fprintf(stderr, "mysql_stmt_execute(), UPDATE failed\n");
        fprintf(stderr, "errno = %d\n", mysql_stmt_errno(stmt));
        fprintf(stderr, "error = %s\n", mysql_stmt_error(stmt));
        mysql_stmt_close(stmt);
        return -1;
    }

    // 关闭预处理语句
    mysql_stmt_close(stmt);

    // 检查是否有行受到影响
    if (mysql_affected_rows(conn) <= 0) {
        fprintf(stderr, "No rows were affected by the UPDATE operation.\n");
        return -1;
    }

    printf("Record updated successfully.\n");
    return 0;
}